package dbtools;

/**
 * Title: SQL语句操作相关静态方法
 * Desc:  用于除DB2之外 的其它数据库的操作
 * 
 */

import dbtools.GlobalParam;

import java.net.ProxySelector;
import java.sql.*;
import java.util.ArrayList;

public class DBOperatorOthe {
	/**
	 * 取得数据库连接
	 * @param nowConn Connect 连接名称
	 */
	public static final Connection getConnect(String dbname) {
		Connection con =null;
		try {
			String url = "";
			String sjkurl = "";
			String sjkuser = "";
			String sjkpwd = "";
 
			dbname = dbname.toLowerCase().trim();
 
			sjkurl =GlobalParam.url; //url
			sjkuser =GlobalParam.username; //数据库用户
			sjkpwd = GlobalParam.password; //数据库口令
			if(sjkurl != null)
			   sjkurl = sjkurl.trim();
			if(sjkuser != null)
			   sjkuser = sjkuser.trim();
			if(sjkpwd != null)
			   sjkpwd = sjkpwd.trim();
            url = sjkurl.toLowerCase();
 
			Class.forName("oracle.jdbc.OracleDriver");
			DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); 
	 
 
			ProxySelector ps = ProxySelector.getDefault();

			ProxySelector.setDefault(null);

			con = DriverManager.getConnection(sjkurl,sjkuser,sjkpwd);
			ProxySelector.setDefault(ps); 
			return con;
		} catch (Throwable t) {
			t.printStackTrace();
			return null;
		} finally {
		}
	}
	/**
	 * 释放数据库连接
	 * @param nowConn Connect 连接名称
	 */
	public static final void freeConnect(Connection nowConn) {
		try {
			nowConn.close();
		} catch (Throwable t) {
			t.printStackTrace();
		} finally {
		}
	}
	/**
	 * 执行表DML语句操作
	 * @String  DML语句
	 * @Connect 表连接
	 * @Return String  ok/error
	 */
	public static final String execDML(String dbname, String strSql) {
		Connection con = getConnect(dbname);

		if (con == null)
			return "error";
		Statement stmtDB;
		ResultSet rsetTemp;
		int nCode;
		String tableName = "";
		try {
			stmtDB = con.createStatement();
			String strTemp = strSql.toLowerCase().trim();

			if (strTemp.startsWith("create")
				|| strTemp.startsWith("drop")
				|| strTemp.startsWith("alter")
				|| strTemp.startsWith("update")) {

				tableName = strTemp;
				stmtDB.executeUpdate(strSql);
				stmtDB.close();
				stmtDB = null;
				rsetTemp = null;
				con.close();
				con = null;
				return "ok";
			} else {
				System.err.println("not dml format!");
				stmtDB.close();
				stmtDB = null;
				rsetTemp = null;
				con.close();
				con = null;
				return "error";
			}
		} catch (SQLException e) {
			e.printStackTrace();
			stmtDB = null;
			rsetTemp = null;
			try{  
			   con.close();
			}catch(Exception e1){
			}
			con = null;
			return e.toString();
		} finally {
			stmtDB = null;
			rsetTemp = null;
			con = null;
		}
	}
	/**
	 * 执行数据表 增删改SQL 事务
	 * @param ArrayList  执行操作语句组
	 * @return String  ok--成功   error--未成功
	 */
	public static final String execSql(String dbname, ArrayList sqls) {
		Statement stmtDB = null;
 
		if (sqls.size() < 1) {
			return "ok";
		}
		DBConnectionManage tmpman = DBConnectionManage.getInstance();

		Connection con = getConnect(dbname);
		if (con == null) {
			return "error";
		}
		try {
			con.setAutoCommit(false);
			stmtDB = con.createStatement();

			for (int i = 0; i < sqls.size(); i++) {
				System.err.println((String) sqls.get(i));
				String tmpsql = ((String) sqls.get(i)).trim();
				if (tmpsql.endsWith(";")) {
					tmpsql = tmpsql.substring(0, tmpsql.length() - 1);
				}
				stmtDB.executeUpdate(tmpsql);
			}
			con.commit();
			con.setAutoCommit(true);

			stmtDB.close();
			stmtDB = null;
			con.close();
			con = null;

			return "ok";
		} catch (SQLException se) {
			se.printStackTrace();
			try {
				con.rollback();
				con.setAutoCommit(true);
				stmtDB.close();
				con.close();
				con = null;
				return String.valueOf(se.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				try {
					con.close();
					con = null;

				} catch (SQLException se2) {
					se2.printStackTrace();
					return "error";
				}
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			stmtDB = null;
		}
	}
	/**
	 * 执行数据表 增删改 单句操作
	 *
	 * @param str 执行操作语句
	 * @return-- (String) "ok" 成功 "error" 未成功)
	 *
	 */
	public static final String execSql(String dbname, String str) {
		Statement stmtDB = null;
 
		Connection con = getConnect(dbname);
		if (con == null)
			return "error";
		try {
			stmtDB = con.createStatement();
			System.err.println(str);
			if (str.endsWith(";")) {
				str = str.substring(0, str.length() - 1);
			}

			stmtDB.executeUpdate(str);
			stmtDB.close();
			stmtDB = null;
			con.close();

			con = null;
			return "ok";
		} catch (Exception se) {
			se.printStackTrace();
			try {
				con.rollback();
				con.setAutoCommit(true);
				stmtDB.close();
				con.close();
				con = null;
				return String.valueOf(se.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				try {
					con.close();
					con = null;
					return String.valueOf(se1.toString());
				} catch (SQLException se2) {
					se2.printStackTrace();
					return "error";
				}
			}
		} finally {
			con = null;
			stmtDB = null;
		}
	}
	/**
	 * 构造分页结果集SQL语句
	 * @param  sql         ：select 语句
	 * @param  fromsql     : from   语句
	 * @param  wheresql    : where  语句
	 * @param  orderSql    : ORDER  语句
	 * @param  startIndex  : 起始位置
	 * @param  pageSize    : 每页记录数
	 * @param  groupsql    : GROUP  语句
	 * @param  addsql      : addsql 语句
	 * @return String
	 */
	public static final String initQuerySQL(
		String sql,
		String fromsql,
		String wheresql,
		String orderSql,
		int startIndex,
		int pageSize,
		String groupsql,
		String addsql) {
		StringBuffer querySQL = new StringBuffer();
 
		int totrow = pageSize + startIndex;
/*		querySQL.append("select * from (select my_table.*,rownum as my_rownum from(")
			.append(
				sql
					+ " "
					+ fromsql
					+ " "
					+ wheresql
					+ " "
					+ groupsql
					+ " "
					+ orderSql)
			.append(") my_table ")
			.append(") where my_rownum>")
			.append(startIndex + " and my_rownum<=" + totrow);
 
		return addsql + " " + querySQL.toString();*/


/*	*//*	StringBuffer querySQL = new StringBuffer();*//*
		int totrow = pageSize + startIndex;*/
		querySQL.append(addsql + " " + sql + " " + fromsql + " " + wheresql
				+ " " + groupsql + " " + orderSql + " limit " + startIndex
				+ "," + pageSize);

		return querySQL.toString();
	}

}


